package com.wlhse.service.impl;

import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.wlhse.dao.CheckItemDao;
import com.wlhse.dao.CheckItemStardardDao;
import com.wlhse.dao.CheckListDao;
import com.wlhse.dao.ProblemDescriptionDao;
import com.wlhse.dto.CheckListDto;
import com.wlhse.dto.EmployeeExcelDto;
import com.wlhse.dto.PoiProblemDto;
import com.wlhse.dto.inDto.HSEImportDto;
import com.wlhse.dto.inDto.HSEImportMapDto;
import com.wlhse.entity.CheckItemPojo;
import com.wlhse.entity.ProblemDescriptionPojo;
import com.wlhse.service.UploadService;
import com.wlhse.util.*;
import com.wlhse.util.PoiCheckItemV2.PoiImplV1;
import com.wlhse.util.PoiCheckItemV2.PoiTreeCheckItem;
import com.wlhse.util.state_code.CodeDict;
import com.wlhse.util.state_code.NR;
import com.wlhse.util.thread.PoiProblemsThread;
import org.apache.commons.beanutils.BeanUtils;
//import org.apache.poi.hwpf.HWPFDocument;
//import org.apache.poi.hwpf.usermodel.Range;
//import org.apache.poi.hwpf.usermodel.TableIterator;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.wp.usermodel.Paragraph;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@Service
public class UploadServiceImpl implements UploadService {

    @Resource
    private PoiProblemUtil poiProblemUtil;

    @Resource
    private PoiUtil poiUtil;

    @Resource
    private GetBeanListByExcel getBeanListByExcel;

    @Resource
    private PoiProblemDescriptions poiProblemDescriptions;

    @Resource
    private ProblemDescriptionDao problemDescriptionDao;

    @Resource
    private CheckItemDao checkItemDao;

    @Resource
    private CheckItemStardardDao checkItemStandardDao;

    @Resource
    private CheckListDao checkListDao;

    private final static String staus = "启用";

    @Override
    @Async
    public void uploadProblemExcel(String path) {
        Workbook workbook = poiUtil.createWorkbook(path);
        poiProblemUtil.excelToDB(workbook);
    }

    @Override
    @Async
    @Transactional
    public void uploadCheckItemExcel(String path) {
        PoiTreeCheckItem util = new PoiTreeCheckItem(new PoiImplV1(), path, CheckItemPojo.class);
        List<String> root = util.getTreeRoot(0, 0);
        Map map = util.treeDict(root, checkItemDao.queryCheckItemPojo(), 4);
        util.poiTree(4, 1, 3, map, root, 4);
        checkItemDao.insertManyCheckItemPojo(util.getResult(), staus);
        checkItemStandardDao.insertManyCheckItemStandardPojo(util.getCheckItemStandardList(), staus);
    }

    @Override
    public void testExcel(String path) {
        Workbook wb = null;
        try {
            wb = WorkbookFactory.create(new File(path));
            readExcel(wb, 0, 0, 0);
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        } catch (IOException | org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void testWord(String path) throws IOException {
        List<HSEImportDto> lists = new ArrayList<>();//接收从Excel中读出来映射的实体
        HSEImportDto hSEImportDto = new HSEImportDto();
        HashMap<String, String> result = new HashMap<>();//存放最终结果
        HashSet<HSEImportMapDto>hs=new HashSet<>();//存放最终放入数据库的集合
        HashMap<String, Integer> index1 = new HashMap<>();//判断子节点序号大小
        HashMap<String, Integer> index2 = new HashMap<>();//判断子节点序号大小
        HashMap<String, Integer> index3 = new HashMap<>();//判断子节点序号大小
        HashMap<String, String> mid = new HashMap<>();//存放最后一级节点的值
        String s11="";String s22="";String s33="";
        int num=0;
        InputStream is = new FileInputStream(path);
        XWPFDocument doc = new XWPFDocument(is);
        List<XWPFTable> tables = doc.getTables();//获取文档中所有的表格
        List<XWPFTableRow> rows;
       // List<XWPFTableCell> cells;
        for (XWPFTable table : tables) { //获取表格对应的行
            rows = table.getRows();
            for (XWPFTableRow row : rows) {
                if("".equals(row.getCell(0).getText())) hSEImportDto.setTheme(s11);//第一单元格
                else {
                    hSEImportDto.setTheme(row.getCell(0).getText());
                    s11=row.getCell(0).getText();//存入s11
                }
                if("".equals(row.getCell(1).getText())) hSEImportDto.setItems(s22);//第二单元格
                else {
                    hSEImportDto.setItems(row.getCell(1).getText());
                    s22=row.getCell(1).getText();//存入s22
                }
                if("".equals(row.getCell(2).getText())) hSEImportDto.setContent(s33);//第三单元格
                else {
                    hSEImportDto.setContent(row.getCell(2).getText());
                    s33=row.getCell(2).getText();//存入s33
                }
                    hSEImportDto.setAssessment(row.getCell(3).getText());
                    hSEImportDto.setInstructions(row.getCell(4).getText());
                lists.add(hSEImportDto);
                for (HSEImportDto pojo : lists) {
                    if (!result.containsKey(pojo.getTheme())) {           //第一层集合中没有的话添加
                        if(num<=9) result.put(pojo.getTheme(), "0" + num);//放入全局map中
                        else result.put(pojo.getTheme(),""+num);
                        num++;
                        index1.put(pojo.getTheme(), 0);//判断本身有几个孩子节点
                    }
                    if (!result.containsKey(pojo.getItems())) {         //第二级结果中没有
                        Integer s = index1.get(pojo.getTheme());//得到编号
                        index1.put(pojo.getTheme(), s + 1);//编号加一放回
                        result.put(pojo.getItems(), result.get(pojo.getTheme()) + "0" + (s + 1));
                        index2.put(pojo.getItems(), 0);//判断本身有几个孩子节点
                    }
                    if(!result.containsKey(pojo.getContent())){        //第三级没有
                        Integer s1 = index2.get(pojo.getItems());//得到编号
                        index2.put(pojo.getItems(), s1+1);//编号加一放回
                        result.put(pojo.getContent(), result.get(pojo.getItems())+"0"+(s1+1));
                        index3.put(pojo.getContent(), 0);//判断本身有几个孩子节点
                    }
                    if(!result.containsKey(pojo.getAssessment())){      //第四级没有
                        Integer s2 = index3.get(pojo.getContent());//得到编号
                        index3.put(pojo.getContent(), s2+1);//编号加一放回
                        result.put(pojo.getAssessment(), result.get(pojo.getContent())+"0"+(s2+1));
                        mid.put(pojo.getAssessment(),pojo.getInstructions());
                    }
                }
            }
        }

        if (is != null) {//关闭流
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //遍历map
        for (Map.Entry<String, String> entry : result.entrySet()) {
            HSEImportMapDto hSEImportMapDto=new HSEImportMapDto();
            hSEImportMapDto.setStatus("启用");
            hSEImportMapDto.setType("质量审核");
            hSEImportMapDto.setHseAuditSystemCode(entry.getValue());
            hSEImportMapDto.setEvalueItem(entry.getKey());
            if(mid.containsKey(entry.getKey())) hSEImportMapDto.setEvalueDescritption(mid.get(entry.getKey()));
            hs.add(hSEImportMapDto);
        }
        List<HSEImportMapDto> pojos=new ArrayList<>();
        pojos.addAll(hs);
        //批量插入数据库
        checkListDao.banchHSEImportMap(pojos);
    }

    public void readExcel(Workbook wb, int sheetIndex, int startReadLine, int tailLine) {
        List<HSEImportDto> lists = new ArrayList<>();//接收从Excel中读出来映射的实体
        HSEImportDto hSEImportDto = new HSEImportDto();
        HashMap<String, String> result = new HashMap<>();//存放最终结果
        HashSet<HSEImportMapDto>hs=new HashSet<>();//存放最终放入数据库的集合
        HashMap<String, Integer> index1 = new HashMap<>();//判断子节点序号大小
        HashMap<String, Integer> index2 = new HashMap<>();//判断子节点序号大小
        HashMap<String, Integer> index3 = new HashMap<>();//判断子节点序号大小
        HashMap<String, String> mid = new HashMap<>();//存放最后一级节点的值
        int num = 0;
        DataFormatter dataFormat = new DataFormatter();//格式化
        Sheet sheet = wb.getSheetAt(sheetIndex);
        Row row = null;
        for (int i = startReadLine; i < sheet.getLastRowNum() - tailLine + 1; i++) {
            row = sheet.getRow(i);
            if (isMergedRegion(sheet, i, 0)) hSEImportDto.setTheme(getMergedRegionValue(sheet, row.getRowNum(), 0));
            else hSEImportDto.setTheme(dataFormat.formatCellValue(row.getCell(0)));
            if (isMergedRegion(sheet, i, 1)) hSEImportDto.setItems(getMergedRegionValue(sheet, row.getRowNum(), 1));
            else hSEImportDto.setItems(dataFormat.formatCellValue(row.getCell(1)));
            if (isMergedRegion(sheet, i, 2)) hSEImportDto.setContent(getMergedRegionValue(sheet, row.getRowNum(), 2));
            else hSEImportDto.setContent(dataFormat.formatCellValue(row.getCell(2)));
            if (isMergedRegion(sheet, i, 3)) hSEImportDto.setAssessment(getMergedRegionValue(sheet, row.getRowNum(), 3));
            else hSEImportDto.setAssessment(dataFormat.formatCellValue(row.getCell(3)));
            if (isMergedRegion(sheet, i, 4)) hSEImportDto.setInstructions(getMergedRegionValue(sheet, row.getRowNum(), 4));
            else hSEImportDto.setInstructions(dataFormat.formatCellValue(row.getCell(4)));
            lists.add(hSEImportDto);
            System.out.println(lists);//每一行数据都变成一个实体类
            for (HSEImportDto pojo : lists) {
                if (!result.containsKey(pojo.getTheme())) {           //第一层集合中没有的话添加
                    System.out.println("0" + num);
                    result.put(pojo.getTheme(), "0" + num);//放入全局map中
                    num++;
                    index1.put(pojo.getTheme(), 0);//判断本身有几个孩子节点
                }
                if (!result.containsKey(pojo.getItems())) {         //第二级结果中没有
                        Integer s = index1.get(pojo.getTheme());//得到编号
                        index1.put(pojo.getTheme(), s + 1);//编号加一放回
                        System.out.println("查看编号：" + index1.get(pojo.getTheme()));
                        result.put(pojo.getItems(), result.get(pojo.getTheme()) + "0" + (s + 1));
                        index2.put(pojo.getItems(), 0);//判断本身有几个孩子节点
                }
                if(!result.containsKey(pojo.getContent())){        //第三级没有
                            Integer s1 = index2.get(pojo.getItems());//得到编号
                            index2.put(pojo.getItems(), s1+1);//编号加一放回
                            result.put(pojo.getContent(), result.get(pojo.getItems())+"0"+(s1+1));
                            index3.put(pojo.getContent(), 0);//判断本身有几个孩子节点
                }
                if(!result.containsKey(pojo.getAssessment())){      //第四级没有
                                Integer s2 = index3.get(pojo.getContent());//得到编号
                                index3.put(pojo.getContent(), s2+1);//编号加一放回
                                result.put(pojo.getAssessment(), result.get(pojo.getContent())+"0"+(s2+1));
                                mid.put(pojo.getAssessment(),pojo.getInstructions());
                }
            }
        }
        //遍历map
        for (Map.Entry<String, String> entry : result.entrySet()) {
            HSEImportMapDto hSEImportMapDto=new HSEImportMapDto();
            hSEImportMapDto.setStatus("启用");
            hSEImportMapDto.setType("HSE审核");
            hSEImportMapDto.setHseAuditSystemCode(entry.getValue());
            hSEImportMapDto.setEvalueItem(entry.getKey());
            if(mid.containsKey(entry.getKey())) hSEImportMapDto.setEvalueDescritption(mid.get(entry.getKey()));
            hs.add(hSEImportMapDto);
            //System.out.println(hSEImportMapDto);
        }
        List<HSEImportMapDto> pojos=new ArrayList<>();
        pojos.addAll(hs);
        //批量插入数据库
        checkListDao.banchHSEImportMap(pojos);
    }
    /**
     * 获取合并单元格的值
     */
    public String getMergedRegionValue(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getCellValue(fCell);
                }
            }
        }
        return null;
    }

    /**
     * 判断合并了行
     */
    private boolean isMergedRow(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row == firstRow && row == lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     * @param sheet
     * @param row    行下标
     * @param column 列下标
     * @return
     */
    private boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 判断sheet页中是否含有合并单元格
     * @param sheet
     * @return
     */
    private boolean hasMerged(Sheet sheet) {
        return sheet.getNumMergedRegions() > 0 ? true : false;
    }

    /**
     * 合并单元格
     * @param sheet
     * @param firstRow 开始行
     * @param lastRow  结束行
     * @param firstCol 开始列
     * @param lastCol  结束列
     */
    private void mergeRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
        sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    }

    /**
     * 获取单元格的值
     *
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell) {

        if (cell == null)
            return "";

        if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

            return cell.getStringCellValue();

        } else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

            return String.valueOf(cell.getBooleanCellValue());

        } else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

            return cell.getCellFormula();

        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

            return String.valueOf(cell.getNumericCellValue());

        }
        return "";
    }
    @Override
    @Async
    public String uploadEmployees(String path) throws Exception {
        String[] strArray = {"employeeCode", "name", "sex", "birthDay", "joinClubTime", "employeeGroup", "job", "study", "secondCompany", "thirdCompany"};
        Workbook workbook = poiUtil.createWorkbook(path);
        return getBeanListByExcel.getBeanList(workbook, strArray, EmployeeExcelDto.class);
    }

    @Override
    public String uploadProblems(String path) throws Exception {
        String[] strArray = {"orderNumber", "parentCompanyName", "companyName", "checkDate",
                "checkType", "checkItemName1", "checkItemName2", "checkItemName3", "task",
                "process", "description", "descriptionDetail", "recommendRectiMeasure", "responsePersonName",
                "responsePersonParentCompanyName", "responsePersonCompanyName", "penalty", "lostScore"};
        Workbook workbook = poiUtil.createWorkbook(path);
        Sheet sheet = workbook.getSheetAt(0);
        int rowSize = sheet.getLastRowNum();

        //每个线程处理的数量
        int count = 200;
        //开启的线程数
        int runSize = rowSize % count == 0 ? rowSize / count : (rowSize / count) + 1;
        ExecutorService executor = Executors.newFixedThreadPool(runSize);
        CountDownLatch countDownLatch = new CountDownLatch(runSize);
        for (int i = 0; i < runSize; i++) {
            int startIdx = i * count + 1;
            int endIdx = (i + 1) * count;
            if (endIdx >= rowSize) endIdx = rowSize;
            PoiProblemsThread poiProblemsThread = new PoiProblemsThread(startIdx, endIdx, sheet, getBeanListByExcel, countDownLatch);
            executor.execute(poiProblemsThread);
        }
        countDownLatch.await();
        executor.shutdown();
        return getBeanListByExcel.getProblemBeanAndPoi(sheet, strArray, PoiProblemDto.class, 0, 0);
    }

    @Override
    @Async
    public void uploadProblemDescription(String path) {
        Workbook workbook = poiUtil.createWorkbook(path);
        List<ProblemDescriptionPojo> beanList = null;
        try {
            beanList = poiProblemDescriptions.getBeanList(workbook);
        } catch (CloneNotSupportedException e) {
            e.printStackTrace();
        }
        problemDescriptionDao.addManyProblemDescriptions(beanList);
    }

    @Override
    @Transactional
    public String uploadCheckList(String path) throws Exception {
        String[] strArray = {"checkItemNumber","checkListItemName","gist","cause","description","hseFactor","hazardFactorType", "problemRank","score"};
        Workbook workbook = poiUtil.createWorkbook(path);
        Sheet sheet = workbook.getSheetAt(0);
        //获取EXCEL中CheckList的值
        CheckListDto checkListDto=new CheckListDto();
        HashMap<String, String> checkListValueMap = new HashMap<>();
        DataFormatter dataFormat=new DataFormatter();
        Row row=sheet.getRow(1);
        checkListValueMap.put("checkListName",dataFormat.formatCellValue(row.getCell(0)).toString());
        checkListValueMap.put("checkListType",dataFormat.formatCellValue(row.getCell(1)).toString());
        checkListValueMap.put("checkListTypeDetail",dataFormat.formatCellValue(row.getCell(2)).toString());
        checkListValueMap.put("note",dataFormat.formatCellValue(row.getCell(3)).toString());
        checkListValueMap.put("checkListCode",dataFormat.formatCellValue(row.getCell(4)).toString());
        checkListValueMap.put("status","启用");
        //使用BeanUtils将封装的属性注入对象
        BeanUtils.populate(checkListDto, checkListValueMap);
        String id=checkListDao.checkListIsExist(checkListDto);
        if(id==null||"".equals(id)){//插入checklist
            checkListDao.addCheckList(checkListDto);
        }else{
            return NR.getPoiProblemReturn(CodeDict.POI_PROBLEM_Repetition,Integer.parseInt(checkListDto.getCheckListCode()));
        }

        return getBeanListByExcel.getCheckListItemsBeanList(workbook, strArray,checkListDto.getCheckListID());
    }

    @Override
    public String uploadEmployees2(String path) throws Exception {
        String[] strArray = {"name","companyCode","station", "position", "empGroup", "jobTime", "birthday", "positionTime"};
        Workbook workbook = poiUtil.createWorkbook(path);
        return getBeanListByExcel.getEmployeeBeanList(workbook, strArray);
    }
}
